//
//  DatabaseSingleton.m
//  UI-21-豆豆豆瓣
//
//  Created by dllo on 15/11/7.
//  Copyright © 2015年 Wang Hao. All rights reserved.
//

#import "DatabaseSingleton.h"
#import "FMDatabase.h"


@interface DatabaseSingleton ()

@property (nonatomic,retain)FMDatabase *db;

@end


@implementation DatabaseSingleton

+(instancetype)shareDatabaseSingleton{
    static DatabaseSingleton *dataBaseSingleton = nil;
    if(dataBaseSingleton == nil){
        dataBaseSingleton = [[DatabaseSingleton alloc] init];
    }
    return dataBaseSingleton;
}
-(void)openDB
{
    //导入sqlite框架，导入FMDB文件夹
    
    //1.获得数据库文件的路径
    NSString *doc=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *fileName=[doc stringByAppendingPathComponent:@"movie.sqlite"];
    NSLog(@"fileName = %@",fileName);
    
    //2.获得数据库
    self.db = [FMDatabase databaseWithPath:fileName];
    
    //3.打开数据库
    if ([self.db open]) {
        NSLog(@"ok");
        //, genres TEXT NOT NULL, film_locations TEXT NOT NULL, runtime TEXT NOT NULL, rating TEXT NOT NULL, rating_count TEXT NOT NULL, release_date TEXT NOT NULL, actors TEXT NOT NULL, plot_simple TEXT NOT NULL, poster TEXT NOT NULL
        //4.创表
        BOOL result = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS lanou3g (id INTEGER PRIMARY KEY AUTOINCREMENT, movieName TEXT NOT NULL, pic_url TEXT NOT NULL, movieId TEXT NOT NULL);"];
        
        if (result) {
            NSLog(@"创表成功");
        }else{
            NSLog(@"创表失败");
        }
        
        BOOL result1 = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, nameUser TEXT NOT NULL, passUser TEXT NOT NULL, againPass TEXT NOT NULL);"];
        
        if (result1) {
            NSLog(@"创用户表成功");
        }else{
            NSLog(@"创用户表失败");
        }
        BOOL result2 = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS collect (id INTEGER PRIMARY KEY AUTOINCREMENT, genres TEXT NOT NULL, film_locations TEXT NOT NULL, runtime TEXT NOT NULL, rating TEXT NOT NULL, rating_count TEXT NOT NULL, release_date TEXT NOT NULL, actors TEXT NOT NULL, plot_simple TEXT NOT NULL, poster TEXT NOT NULL, nameUser TEXT NOT NULL);"];
        
        if (result2) {
            NSLog(@"创收藏表成功");
        }else{
            NSLog(@"创收藏表失败");
        }

        BOOL result3 = [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS lanou3 (id INTEGER PRIMARY KEY AUTOINCREMENT, movieName TEXT NOT NULL, pic_url TEXT NOT NULL, movieId TEXT NOT NULL,named TEXT NOT NULL);"];
        
        if (result3) {
            NSLog(@"创表成功");
        }else{
            NSLog(@"创表失败");
        }


    }
}


-(void)insertData:(Movie *)movie
{
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO lanou3g (movieName, pic_url, movieId) VALUES ('%@', '%@', '%@')",movie.movieName,movie.pic_url,movie.movieId];
    
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入成功");
        
    }else
    {
        NSLog(@"插入成功");
        
    }
    
}

#pragma mark 插入数据
-(void)insertData:(Movie *)movie name:(NSString *)named
{
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO lanou3 (movieName, pic_url, movieId,named) VALUES ('%@', '%@', '%@', '%@')",movie.movieName,movie.pic_url,movie.movieId, named];
    
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入成功");
        
    }else
    {
        NSLog(@"插入成功");
        
    }
    
}

-(NSMutableArray *)selectData
{
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from lanou3g"];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        Movie *mov = [[Movie alloc] init];
        int idNum = [resultSet intForColumn:@"id"];
        mov.movieName = [resultSet objectForColumnName:@"movieName"];
        mov.pic_url = [resultSet objectForColumnName:@"pic_url"];
        mov.movieId = [resultSet objectForColumnName:@"movieId"];
        
        NSLog(@"id=%i ,movieName=%@, pic_url=%@, movieId=%@",idNum,mov.movieName,mov.pic_url,mov.movieId);
        [array addObject:mov];
    }
    return array;
    
}

#pragma mark 查询数据
-(NSMutableArray *)selectData:(NSString *)named
{
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from lanou3 where named = ?",named];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        Movie *mov = [[Movie alloc] init];
        int idNum = [resultSet intForColumn:@"id"];
        mov.movieName = [resultSet objectForColumnName:@"movieName"];
        mov.pic_url = [resultSet objectForColumnName:@"pic_url"];
        mov.movieId = [resultSet objectForColumnName:@"movieId"];

        NSLog(@"id=%i ,movieName=%@, pic_url=%@, movieId=%@",idNum,mov.movieName,mov.pic_url,mov.movieId);
        [array addObject:mov];
    }
    return array;
    
}

-(Movie *)selectData1:(NSString *)named
{
    //NSMutableArray *array = [NSMutableArray array];
    Movie *mov = [[Movie alloc] init];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from lanou3 where named = ?",named];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        int idNum = [resultSet intForColumn:@"id"];
        mov.movieName = [resultSet objectForColumnName:@"movieName"];
        mov.pic_url = [resultSet objectForColumnName:@"pic_url"];
        mov.movieId = [resultSet objectForColumnName:@"movieId"];
        
        NSLog(@"id=%i ,movieName=%@, pic_url=%@, movieId=%@",idNum,mov.movieName,mov.pic_url,mov.movieId);
        //[array addObject:mov];
    }
    return mov;
    
}

#pragma mark 销毁表格
-(void)dropTable
{
    [self.db executeUpdate:@"DROP TABLE IF EXISTS lanou3g"];
    
    //4.创表
    BOOL result=[self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS lanou3g (id integer PRIMARY KEY AUTOINCREMENT, movieName TEXT NOT NULL, pic_url TEXT NOT NULL, movieId TEXT NOT NULL)"];
    
    if (result) {
        NSLog(@"再次创表成功");
    }else{
        NSLog(@"再次创表失败");
    }
}

///////////////////////用户
#pragma mark 插入数据
-(void)insertUser:(Users *)userO
{
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO users (nameUser, passUser, againPass) VALUES ('%@', '%@', '%@')",userO.nameUser,userO.passUser,userO.againPass];
    
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入成功");
        
    }else
    {
        NSLog(@"插入成功");
        
    }
    
}
#pragma mark 查询数据
-(Users *)selectUser:(NSString *)nameUser
{
    Users *u = [[Users alloc] init];
    //NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from users where nameUser = ?", nameUser];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        
        int idNum = [resultSet intForColumn:@"id"];
        u.nameUser = [resultSet objectForColumnName:@"nameUser"];
        u.passUser = [resultSet objectForColumnName:@"passUser"];
        u.againPass = [resultSet objectForColumnName:@"againPass"];
        NSLog(@"id=%i ,nameUser=%@, passUser=%@, againPass=%@",idNum,u.nameUser,u.passUser,u.againPass);
        //[array addObject:u];
    }
    return u;
    
}

///////////////////////收藏

-(void)insertcollect:(Movie *)movie name:(NSString *)nameUser
{
    //如果用stringWithFormat时,sql语句中可以用'%@'
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO collect (genres, film_locations, runtime, rating, rating_count, release_date, actors, plot_simple, poster,nameUser) VALUES ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')",movie.genres,movie.film_locations,movie.runtime,movie.rating,movie.rating_count,movie.release_date,movie.actors,movie.plot_simple,movie.poster,nameUser];
    //, genres TEXT NOT NULL, film_locations TEXT NOT NULL, runtime TEXT NOT NULL, rating TEXT NOT NULL, rating_count TEXT NOT NULL, release_date TEXT NOT NULL, actors TEXT NOT NULL, plot_simple TEXT NOT NULL, poster TEXT NOT NULL
    BOOL result = [self.db executeUpdate:sql];
    if (result) {
        
        NSLog(@"插入成功");
        
    }else
    {
        NSLog(@"插入成功");
        
    }
    
}

#pragma mark 查询数据
-(NSMutableArray *)selectcollect:(NSString *)nameUser
{
    NSMutableArray *array = [NSMutableArray array];
    //1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"select * from collect where nameUser = ?", nameUser];
    
    //2.遍历结果集合
    while ([resultSet next]) {
        Movie *mov = [[Movie alloc] init];
        int idNum = [resultSet intForColumn:@"id"];
        
            mov.genres = [resultSet objectForColumnName:@"genres"];
            mov.film_locations = [resultSet objectForColumnName:@"film_locations"];
            mov.runtime = [resultSet objectForColumnName:@"runtime"];
            mov.rating = [resultSet objectForColumnName:@"rating"];
            mov.rating_count = [resultSet objectForColumnName:@"rating_count"];
            mov.release_date = [resultSet objectForColumnName:@"release_date"];
            mov.actors = [resultSet objectForColumnName:@"actors"];
            mov.plot_simple = [resultSet objectForColumnName:@"plot_simple"];
            mov.poster = [resultSet objectForColumnName:@"poster"];
        NSLog(@"id=%i ,release_date=%@",idNum,mov.release_date);
        [array addObject:mov];
    }
    return array;
    
}
#pragma mark 删除数据
-(void)deletecollect:(NSString *)release_date
{
    
    NSString *deleteSql = [NSString stringWithFormat:
                           @"DELETE FROM collect WHERE release_date = '%@'",release_date];
    BOOL res = [self.db executeUpdate:deleteSql];
    
    if (res) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}




@end
